package com.ce.pms.charge.dao.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.hibernate.Query;

import com.ce.pms.base.dao.impl.PmsBaseDaoImpl;
import com.ce.pms.charge.dao.IMeterDataRegisterDao;
import com.ce.pms.charge.model.ChargeDatasDomain;
import com.ce.pms.charge.service.impl.MeterDataRegisterServiceImpl.Conditions;
import com.ce.pms.common.annotation.AssociatedBuildingQuery;
import com.ce.pms.common.annotation.AssociatedPrecinctQuery;
import com.ce.pms.common.model.SysrefcodeDO;
import com.ce.util.BlankUtil;
import com.sitechasia.webx.core.support.Page;

public class MeterDataRegisterDaoImpl extends PmsBaseDaoImpl<ChargeDatasDomain> implements
		IMeterDataRegisterDao {

	/**
	 * 方法描述: 分页组合查询抄表数据方法
	 * @author 胡煦
	 * Create on:2008-8-13
	 * @param buildIdStr,recordTimeStr,meterTypeStr,periodIdStr,statuzStr,houseNumberStr,currentPage, pageSize
	 * @return page
	 * Edit author:
	 * Edit on:
	 * 修改原因：
	 */
	@AssociatedBuildingQuery(alias = "cd", beginStr = " AND ", endStr = " ", insertAfterPoint = "WHERE 1=1",property4Building="house.building.keyword")
	public Page getChargeDatasDomainsByPage(Conditions conditions,int currentPage,int pageSize){

		Long buildingId = conditions.getBuildingId();
		Long proRightId = conditions.getProRightId();
		Date recordTime = conditions.getRecordTime();
		Long meterTypeId = conditions.getMeterTypeId();
		Long periodId = conditions.getPeriodId();
		String statuz = conditions.getStatuz();
		Long houseId = conditions.getHouseId();
		String houseIds = conditions.getHouseIds();
		String houseNumber = conditions.getHouseNumber();
		String order = conditions.getOrder();
		String buildIdStr = "cd.house.building.id = ? AND ";
		String proRightIdStr = "cd.proRight.id = ? AND ";
		String recordTimeStr = "cd.recordTime = ? AND ";
		String meterTypeStr = "cd.meter.meterType.id =? AND ";
		String periodIdStr = "cd.period.id =? AND ";
		String statuzStr = "cd.statuz =? AND ";
		String houseIdStr = "cd.house.id =? AND ";
		String houseIdsStr = "cd.house.id in (:houseIds) AND ";
		String houseNumberStr = "cd.house.houseNumber like ? AND ";
		String orderStr = "order by ?";
		String hql = "FROM ChargeDatasDomain cd WHERE 1=1 AND :buildIdStr :proRightIdStr :recordTimeStr :meterTypeStr :periodIdStr :statuzStr :houseIdStr :houseIdsStr :houseNumberStr ";

		List<Object> paramList = new ArrayList<Object>();
		int i = 0;
		if (!BlankUtil.isBlank(buildingId)) {
			paramList.add(i++, buildingId);
		}else
			buildIdStr="";

		if (!BlankUtil.isBlank(proRightId)) {
			paramList.add(i++, proRightId);
		}else
			proRightIdStr="";

		if (!BlankUtil.isBlank(recordTime)) {
			paramList.add(i++, recordTime);
		}else
			recordTimeStr="";

		if (!BlankUtil.isBlank(meterTypeId)) {
			paramList.add(i++, meterTypeId);
		}else
			meterTypeStr="";

		if (!BlankUtil.isBlank(periodId)) {
			paramList.add(i++, periodId);
		}else
			periodIdStr="";

		if (!BlankUtil.isBlank(statuz)) {
			if(statuz.equals(SysrefcodeDO.FEE_CHARGE_DATAS_STATUS_ABATE)){
				statuzStr = "cd.statuz in ('003','004','005') AND ";
				//hql = "FROM ChargeDatasDomain cd WHERE :buildIdStr :recordTimeStr :meterTypeStr :periodIdStr :houseNumberStr ";
			}else{
				paramList.add(i++, statuz);
				}
		}else
			statuzStr="";

		if (!BlankUtil.isBlank(houseId)) {
			paramList.add(i++, houseId);
		}else
			houseIdStr="";

		if (!BlankUtil.isBlank(houseIds)) {
			houseIdsStr = houseIdsStr.replace(":houseIds", houseIds);
		}else
			houseIdsStr="";

		if (!BlankUtil.isBlank(houseNumber)) {
			paramList.add(i++, "%"+houseNumber+"%");
		}else
			houseNumberStr="";



		hql = hql.replace(":buildIdStr "  , buildIdStr)
		 .replace(":proRightIdStr " , proRightIdStr)
		 .replace(":recordTimeStr " , recordTimeStr)
		 .replace(":meterTypeStr " , meterTypeStr)
		 .replace(":periodIdStr ", periodIdStr)
		 .replace(":statuzStr "  , statuzStr)
		 .replace(":houseIdStr " , houseIdStr)
		 .replace(":houseIdsStr " , houseIdsStr)
		 .replace(":houseNumberStr " , houseNumberStr);

		if (hql.endsWith("AND ")) {
			hql = hql.substring(0, hql.lastIndexOf("AND "));
		}

		if (hql.endsWith("WHERE ")) {
			hql = hql + "1=1";
		}

		if (!BlankUtil.isBlank(order)) {
			paramList.add(i++, "cd."+order);
		}else
			orderStr="order by cd.id desc";
		hql = hql + " :orderStr";
		hql = hql.replace(":orderStr" , orderStr);

		Page page = pagedQueryByPageNo(hql, currentPage, pageSize, paramList.toArray());
		return page;

	}

	/**
	 *
	 * 功能描述: 根据房屋ID和走表ID查询某个收费周期下有效的抄表数据
	 *
	 * @author 胡煦
	 * Created on:2008-7-14
	 *
	 * 修改原因：
	 * @param houseId
	 * @return MeterDomain 返回结果
	 */
	public ChargeDatasDomain getMeterDatasListByHouseIdAndMeterId(Long houseId,Long meterId){

		if (BlankUtil.isBlank(houseId)) {
			return null;
		}


		String queryStr = "FROM ChargeDatasDomain cd WHERE cd.house.id = ? and cd.meter.id = ? and cd.statuz in ('001','002') order by cd.id desc";

		Query query = super.createQuery(queryStr, new Object[]{houseId,meterId});
		List list = query.list();
		if(!BlankUtil.isBlank(list)){
		ChargeDatasDomain chargeDatasDomain = (ChargeDatasDomain) list.get(0);
		return chargeDatasDomain;
		}
		return null ;

	}

	/**
	 * 方法描述: 查询所有抄表数据方法
	 * @author 胡煦
	 * Create on:2008-8-13
	 * @param
	 * @return List<ChargeDatasDomain>
	 * Edit author:
	 * Edit on:
	 * 修改原因：
	 */
	public List<ChargeDatasDomain> getAllChargeDatasDomainList(){
		List<ChargeDatasDomain> list = this.findAll();
		return list;
	}

	/**
	 * 方法描述: 查询最大的抄表时间
	 * @author 胡煦
	 * Create on:2008-8-21
	 * @param nameStr：查询条件班级名称
	 * @return Date
	 * Edit author:
	 * Edit on:
	 * 修改原因：
	 */
	public Date getMaxRecordTime(){

		String queryStr = "SELECT MAX(cd.recordTime) FROM ChargeDatasDomain cd";
		Query query = super.createQuery(queryStr);
		Date date = (Date) query.uniqueResult();
		return date;
	}

	/**
	 * 方法描述: 批量提交抄表数据
	 * @author 胡煦
	 * Create on:2008-9-9
	 * @param List<Object[]> list
	 * @return boolean
	 * Edit author:
	 * Edit on:
	 * 修改原因：
	 */
	public boolean batchInsertChargeDatas(List<Object[]> list){
		String sql = "insert into FEE_CHARGE_DATAS ( HOUSE_ID,PRO_RIGHT_ID,CUST_NAME,PERIOD_ID,METER_ID,STATUZ,LAST_READING,THIS_READING,WASTAGE,RECORD_TIME,CREATOR,SYSDATE ) values( ?,?,?,?,?,?,?,?,?,?,?,now() )";
		this.executeSQLBatch(sql, list);
		return true;
	}

	/**
	 *
	 * 功能描述: 通过房号id查询走表实体List
	 *
	 * @author 胡煦
	 * Created on:2008-7-14
	 *
	 * 修改原因：
	 * @param houseId
	 * @return MeterDomain 返回结果
	 */
	public List<ChargeDatasDomain> getMeterDatasListByHouseId(Long houseId){

		if (BlankUtil.isBlank(houseId)) {
			return null;
		}

		List<ChargeDatasDomain> list = new ArrayList<ChargeDatasDomain>();

		String queryStr = "FROM ChargeDatasDomain cd WHERE cd.house.id = ? ";

		Query query = super.createQuery(queryStr, houseId);

		list = query.list();

		return list ;

	}

	/**
	 *
	 * 功能描述: 通过楼栋编号，房号，走表名称查询当前有效收费周期下为新建状态的走表实体List
	 *
	 * @author 胡煦
	 * Created on:2008-7-14
	 *
	 * 修改原因：
	 * @param houseId
	 * @return MeterDomain 返回结果
	 */
	public List<ChargeDatasDomain> getMeterDatasListByNames(int seqNo, String houseNumber,String meterTypeName){

		if (BlankUtil.isBlank(seqNo)||BlankUtil.isBlank(houseNumber)||BlankUtil.isBlank(meterTypeName)) {
			return null;
		}

		List<ChargeDatasDomain> list = new ArrayList<ChargeDatasDomain>();

		String queryStr = "FROM ChargeDatasDomain cd WHERE cd.house.building.seqNo = ? and cd.house.houseNumber = ? and cd.meter.meterType.name = ? and cd.period.validity = '1' and cd.statuz = '001'";

		Query query = super.createQuery(queryStr, new Object[]{seqNo,houseNumber,meterTypeName});

		list = query.list();

		return list ;

	}

	/**
	 *
	 * 功能描述: 通过收费周期id查询新建状态的走表实体List
	 *
	 * @author 胡煦
	 * Created on:2008-8-27
	 *
	 * 修改原因：
	 * @param periodId
	 * @return MeterDomain 返回结果
	 */
	@AssociatedPrecinctQuery(alias = "cd", beginStr = " AND ", endStr = " ", insertAfterPoint = "WHERE 1=1" ,property4Precinct="period.chargePrecinct.keyword")
	public List<ChargeDatasDomain> getMeterDatasListByPeriodId(Long periodId){

		List<ChargeDatasDomain> list = new ArrayList<ChargeDatasDomain>();

		if (BlankUtil.isBlank(periodId)) {

			String queryStr = "FROM ChargeDatasDomain cd WHERE 1=1 AND cd.period.validity = '1' and cd.statuz = '001'";

			Query query = super.createQuery(queryStr);

			list = query.list();

			return list ;
		}

		String queryStr = "FROM ChargeDatasDomain cd WHERE 1=1 AND cd.period.id = ? and cd.statuz = '001'";

		Query query = super.createQuery(queryStr, periodId);

		list = query.list();

		return list ;

	}

	/**
	 * 功能描述:查找指定收费周期、房间的新建状态的原始数据<br>
	 * @param periodId
	 * @param houseId
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-10-13<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	public List<ChargeDatasDomain> getNewMeterDatasListByPeriodId(Long periodId,Long houseId){
		String queryStr="FROM ChargeDatasDomain cd WHERE cd.period.id = ? and cd.statuz = '001' and cd.house.keyword=?";
		Query query = super.createQuery(queryStr.toString(), periodId,houseId);
		return query.list() ;
	}

	/**
	 * 功能描述:查找指定房间的新建状态的原始数据<br>
	 * @param houseId
	 * @return<br>
	 * Create author:胡煦<br>
	 * Create on:2008-10-13<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	public List<ChargeDatasDomain> getNewMeterDatasListByHouseId(Long houseId){
		String queryStr="FROM ChargeDatasDomain cd WHERE cd.statuz = '001' and cd.house.id=?";
		Query query = super.createQuery(queryStr.toString(),houseId);
		return query.list() ;
	}

	/**
	 *
	 * 功能描述: 通过楼栋id和走表类型CODE查询有效的走表原始数据实体List,按房号分组并取其中ID最大的
	 *
	 * @author 胡煦
	 * Created on:2008-8-25
	 *
	 * 修改原因：
	 * @param buildingId
	 * @return MeterDomain 返回结果
	 */
	public List getMeterDatasListByBuildingId(Long buildingId,String meterTypeCode){
		if (BlankUtil.isBlank(buildingId)) {
			return null;
		}

		List list = new ArrayList();

		StringBuffer queryStr = new StringBuffer(" SELECT CD.HOUSE_ID,CD.THIS_READING FROM FEE_CHARGE_DATAS CD ").append
		(" INNER JOIN(SELECT MAX(CHARGE_DATA_ID) CHARGE_DATA_ID FROM ").append
		(" FEE_CHARGE_DATAS CD INNER JOIN HM_METER HME ON CD.METER_ID = HME.METER_ID ").append
		(" INNER JOIN HM_METER_TYPE HMT ON HME.METER_TYPE_ID = HMT.METER_TYPE_ID ").append
		(" WHERE CD.STATUZ IN ('001','002') AND HMT.CODE = ? AND HME.VALIDITY = '1' GROUP BY CD.HOUSE_ID) ").append
		(" AS S2 ON CD.CHARGE_DATA_ID=S2.CHARGE_DATA_ID ").append
		(" INNER JOIN HM_HOUSE HMH ON HMH.HOUSE_ID = CD.HOUSE_ID ").append
		(" INNER JOIN HM_BUILDING HMB ON HMB.BUILDING_ID = HMH.BUILDING_ID ").append
		(" WHERE HMB.BUILDING_ID = ? ");

		Query query = super.createSQLQuery(queryStr.toString(), new Object[]{meterTypeCode,buildingId});

		list = query.list();

		return list ;
	}



	/**
	 * 功能描述: 通过楼栋id查询有效走表底数和走表原始数据实体List,按房号分组并取其中ID最大的
	 *
	 * @author 胡煦
	 * Created on:2008-8-25
	 *
	 * 修改原因：
	 * @param buildingId
	 * @return MeterDomain 返回结果
	 */
	public List getMeterDataBasesByBuildingId(Long buildingId,String meterTypeCode){
		if (BlankUtil.isBlank(buildingId)) {
			return null;
		}

		List list = new ArrayList();

		String queryStr = "select cd.house.id,md.base,cd.thisReading FROM ChargeDatasDomain cd right join cd.meter md WHERE cd.statuz in ('001','002') and md.validity = 1 and md.meterType.code = ? and cd.house.building.id = ? group by cd.house order by cd.id desc) ";

		Query query = super.createQuery(queryStr, new Object[]{meterTypeCode,buildingId});

		list = query.list();

		return list ;
	}

	/**
	 *
	 * 功能描述: 根据房屋ID和走表类型查询某个收费周期下有效的抄表数据
	 *
	 * @author 胡煦
	 * Created on:2008-7-14
	 *
	 * 修改原因：
	 * @param houseId
	 * @return MeterDomain 返回结果
	 */
	public ChargeDatasDomain getMeterDatasListByHouseIdAndType(Long houseId,String meterTypeCode){

		if (BlankUtil.isBlank(houseId)) {
			return null;
		}


		String queryStr = "FROM ChargeDatasDomain cd WHERE cd.house.id = ? and cd.meter.validity = '1' and cd.meter.meterType.code = ? and cd.statuz in ('001','002') order by cd.id desc";

		Query query = super.createQuery(queryStr, new Object[]{houseId,meterTypeCode});
		List list = query.list();
		if(!BlankUtil.isBlank(list)){
		ChargeDatasDomain chargeDatasDomain = (ChargeDatasDomain) list.get(0);
		return chargeDatasDomain;
		}
		return null ;

	}

	/**
	 * 功能描述:查询指定收费周期状态为已计算的所有原始数据<br>
	 * @param periodId
	 * @return <br>
	 * Create author:罗军林<br>
	 * Create on:2008-9-19<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	public List<ChargeDatasDomain> queryLastMeterData(Long periodId){
		String hql="SELECT cd FROM ChargeDatasDomain cd JOIN FETCH cd.house house  JOIN FETCH cd.meter WHERE cd.statuz='002' AND cd.period.keyword=? ORDER BY cd.house,cd.meter,cd.chargeDataId DESC";
		Query query=createQuery(hql, periodId);
		return query.list();
	}

	/**
	 * 功能描述:查询指定房间指定走表类型且状态为已计算的抄表时间为最后的原始数据<br>
	 * @param periodId
	 * @return <br>
	 * Create author:罗军林<br>
	 * Create on:2008-9-19<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	public ChargeDatasDomain findLastMeterData(Long houseId,String meterType){
		String hql="FROM ChargeDatasDomain cd WHERE cd.statuz='002' AND cd.house.keyword=? AND cd.meter.meterType.code=? ORDER BY cd.chargeDataId DESC";
		Query query=createQuery(hql, houseId,meterType);
		query.setFirstResult(0);
		query.setMaxResults(1);
		return (ChargeDatasDomain)query.uniqueResult();
	}
}
